package com.situ.dao;

import com.situ.entity.Product;
import com.situ.entity.User;
import com.situ.util.DBUtil;
import com.situ.util.StringUtil;
import com.situ.vo.UserVO;

import java.util.List;

/**
 * @author yangkx
 * @create 2023-08-29-10:04
 */
public class UserDao {

    //登陆判断
    public List<User> selectByUsername(String username) {
        String sql = "select * from user where username = ?";
        return DBUtil.executeDQL(sql,User.class,username);
    }

    //查询表格数据
    public List<UserVO> list(Integer page, Integer limit, String username, String phoneNumber) {
        String sql = "select user.*,type.`name` 'typeName' from user\n" +
                "left join type on type.id=user.type_id";
        //判断
        //username非空 且 phoneNumber非空
        if (StringUtil.isNotBlank(username) && StringUtil.isNotBlank(phoneNumber)){
            sql += " where instr(username,?)>0 and instr(phone_number,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,UserVO.class,username,phoneNumber,(page-1)*limit,limit);
        }
        //username非空
        if(StringUtil.isNotBlank(username)){
            sql += " where instr(username,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,UserVO.class,username,(page-1)*limit,limit);
        }
        //phoneNumber非空
        if (StringUtil.isNotBlank(phoneNumber)){
            sql += " where instr(phone_number,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,UserVO.class,phoneNumber,(page-1)*limit,limit);
        }
        //三个if都不通过，则username 和 phoneNumber都为空，查全部
        sql += " limit ?,?";
        return DBUtil.executeDQL(sql,UserVO.class,(page-1)*limit,limit);
    }

    //保存
    public void insert(User u) {
        String sql = "insert into user(username,password,type_id,phone_number) values(?,?,?,?)";
        DBUtil.executeDML(sql,u.getUsername(),u.getPassword(),u.getTypeId(),u.getPhoneNumber());
    }

    //更新
    public void update(User u) {
        String sql = "update user set username=?,password=?,type_id=?,phone_number=? where id=?";
        DBUtil.executeDML(sql,u.getUsername(),u.getPassword(),u.getTypeId(),u.getPhoneNumber(),u.getId());
    }

    //删除
    public void delete(Integer id) {
        String sql = "delete from user where id = ?";
        DBUtil.executeDML(sql,id);
    }

    //编辑——根据ID查询该行数据带到前台
    public List<User> selectById(Integer id) {
        String sql = "select * from user where id = ?";
        return DBUtil.executeDQL(sql,User.class,id);
    }

    //统计查询结果——根据过滤条件帮我们统计一个Long值 来查询结果表示共有多少条数据
    public Long count(String username, String phoneNumber) {
        String sql = "select count(1) from user";
        //username非空 且 phoneNumber非空
        if (StringUtil.isNotBlank(username) && StringUtil.isNotBlank(phoneNumber)){
            sql += "  where instr(username,?)>0 and instr(phoneNumber,?)>0";
            return DBUtil.executeCount(sql,username,phoneNumber);
        }
        //username非空
        if(StringUtil.isNotBlank(username)){
            sql += "  where instr(username,?)>0";
            return DBUtil.executeCount(sql,username);
        }
        //phoneNumber非空
        if (StringUtil.isNotBlank(phoneNumber)){
            sql += "  where instr(phoneNumber,?)>0";
            return DBUtil.executeCount(sql,phoneNumber);
        }
        return DBUtil.executeCount(sql);
    }
}
